On or about 9/25/2005 4:03 PM, it came to pass that phpuser32423 wrote:
Hi everyone
Is it by any chance possible to use mysql and php to auto create the
content for drop-down lists on forms by retrieving the values from a
database? The reason i ask is that on a site i am making i am asking
users to select from list (e.g. nationality) however i would like to
avoid typing out every possible value of which there are about 40
(which change). I would much prefer to keep a seperate table in my
database for the site.
Kind regards
Mcyi2mr3
You did not say it but this code assumes you are using MySql Enum or Set
fields.
The following php script I wrote will take an Enum or Set field from a
table,creates a Select box or Check Boxes, and could be easily expanded
to have a Select Multiple or whatever you need. It will optionally sort
the field names and set selected elements from the corresponding
database data field. I'm fairly new at PHP and cobbled this together
from various other scripts I found on the net for use on my website. I
am certain someone can find ways to do it better, more elegantly or find
fault with the code, but this works for me. However if you do find a
serious fault please post it in this group.
Apologies for the messy indentation it's caused by the very poor
interaction of my text editor and e-mail program.
$table = Table name
$field = Enum or Set field in $table
$type = select or whatever (defaults to check box)
$data = field value (optional the data from field in table)
$sort = yes or no (sort options ascending)
$title = optional; null first field for a select
<?php
function DrawFromDB($table,$field,$type,$data="",$sort="yes ",$title="")
{
//connect to DB;
$query=mysql_query("SHOW COLUMNS FROM ".$table." LIKE
'".$field."'") or die (mysql_error());
if(mysql_num_rows($query)>0)
{
$row=mysql_fetch_row($query);
$options=explode("','",preg_replace("/(enum|set)\('(.+?)'\)/","\\2",$row[1]));
if ($sort =="yes")
sort ($options);
$ARay = explode(",",$data);
}
switch ($type)
{
case "select":
$text="\n\n<select name='". $field ."'>\n";
if ($title > "")
$text.="<option value=\"\">" . $title . "</option>\n";
for ($i=0;$i<count($options);$i++)
{
$selected = NULL;
if ($data == $options[$i])
$selected ="SELECTED ";
$text.="<option " . $selected .
"value=\"".$options[$i]."\">".ucfirst($options[$i])."</option>\n";
}
$text.="</select>\n\n";
break;
default:
$text="\n";
for ($i=0;$i<count($options);$i++)
{
$checked=NULL;
for ($j=0;$j<count($ARay);$j++)
{
if ($ARay[$j] == $options[$i])
$checked=" CHECKED ";
}
$text.="<INPUT TYPE='checkbox'" . $checked . " NAME='" . $field."[]'
VALUE='". $options[$i] . "'>".ucfirst($options[$i])." \n";
}
$text.="\n";
break;
}
return $text;
}
?>
